package org.crate.query.api.utils;

import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.*;

/**
 * Created by huangfei on 2018/9/19.
 */
public class BatchConvert {

    // 检查每行的列数、每行的key的顺序是否一致
    // true：检查通过，false：检查不通过
    public static boolean batchColumnSizeIsOk(List<Map<String, String>> data) throws SQLException {
        // parse fields
        List<String> fields = new ArrayList<String>();
        Map<String, String> one = data.get(0);
        for (Iterator<Map.Entry<String, String>> it = one.entrySet().iterator(); it.hasNext();) {
            fields.add(it.next().getKey());
        }

        Map<String, String> keyCheck = new HashMap<String, String>(1);

        boolean columnSizeIsOk = true;
        for (Iterator<Map<String, String>> it = data.iterator(); it.hasNext();) {
            Map<String, String> entry = it.next();

            StringBuilder keyBuff = new StringBuilder();
            for (String key : entry.keySet()) {
                keyBuff.append(key.trim());
            }

            keyCheck.put(keyBuff.toString(), "1");

            if (entry.size() != fields.size()) {
                columnSizeIsOk = false;
                break;
            }
        }

        if (keyCheck.size() != 1) {
            // System.out.println("WARN: keyCheck=" + keyCheck.size() + "," + keyCheck);
            System.out.println("WARN: keyCheck=" + keyCheck.size());
        }

        if (!columnSizeIsOk) {
            System.out.println("WARN: columnSizeIsOk=" + columnSizeIsOk);
        }

        return columnSizeIsOk && keyCheck.size() == 1;
    }

    @SuppressWarnings("unused")
    public static BatchInput convert(String tablename, List<Map<String, String>> data) throws SQLException {

        // parse fields
        List<String> fields = new ArrayList<String>();
        Map<String, String> one = data.get(0);
        for (Iterator<Map.Entry<String, String>> it = one.entrySet().iterator(); it.hasNext();) {
            fields.add(it.next().getKey());
        }

        // parse batch data
        Object[][] batchData = new Object[data.size()][one.size()];

        boolean columnSizeIsOk = true;
        int i = 0;
        for (Iterator<Map<String, String>> it = data.iterator(); it.hasNext();) {
            Map<String, String> entry = it.next();

            if (entry.size() != fields.size()) {
                columnSizeIsOk = false;
                break;
            }

            int j = 0;
            Iterator<Map.Entry<String, String>> itMap = entry.entrySet().iterator();
            while (itMap.hasNext()) {
                Map.Entry<String, String> ee = itMap.next();
                batchData[i][j++] = ee.getValue();
            }

            i++;
        }

        if (!columnSizeIsOk) {
            return new BatchInput(null, null);
        }

        // create batch sql
        StringBuilder buff = new StringBuilder();
        buff.append("INSERT INTO ").append(tablename).append(" (");

        for (String field : fields) {
            buff.append("\"").append(field.toLowerCase()).append("\"").append(",");
        }
        buff.deleteCharAt(buff.length() - 1);
        buff.append(") VALUES (");
        for (String field : fields) {
            buff.append("?").append(",");
        }
        buff.deleteCharAt(buff.length() - 1);
        buff.append(")");

        return new BatchInput(buff.toString(), batchData);
    }

//    public static String clearTable(String tablename) {
//        StringBuilder buff = new StringBuilder();
//        buff.append("DELETE FROM ").append(tablename);
//        return buff.toString();
//    }

    public static String convertDel(String tablename, Map<String, String> map) {

        StringBuilder buff = new StringBuilder();
        buff.append("DELETE FROM ").append(tablename);

        if (!map.isEmpty()) {
            buff.append(" WHERE ");
        }

        int i = 0;
        for (Iterator<Map.Entry<String, String>> it = map.entrySet().iterator(); it.hasNext();) {
            Map.Entry<String, String> entry = it.next();

            if (StringUtils.isBlank(entry.getValue())) {
                continue;
            }

            buff.append("\"").append(entry.getKey().toLowerCase()).append("\"").append("=").append("'")
                    .append(entry.getValue()).append("'");

            if (i++ != map.size() - 1) {
                buff.append(" AND ");
            }
        }
        return buff.toString();
    }

    public static String convertUpdateUq(String tablename, Map<String, String> setData, Map<String, String> whereMap) {
        if (StringUtils.isBlank(tablename) || setData == null || setData.isEmpty() || whereMap == null
                || whereMap.isEmpty()) {
            throw new NullPointerException();
        }

        StringBuilder buff = new StringBuilder();
        buff.append("UPDATE ").append(tablename).append(" SET ");

        for (Iterator<Map.Entry<String, String>> it = setData.entrySet().iterator(); it.hasNext();) {
            Map.Entry<String, String> entry = it.next();

            if (StringUtils.isBlank(entry.getValue())) {
                continue;
            }

            buff.append("\"").append(entry.getKey().toLowerCase()).append("\"").append("=").append("'")
                    .append(entry.getValue()).append("',");
        }
        buff.deleteCharAt(buff.length() - 1);

        buff.append(" WHERE 1=1 ");
        for (Iterator<Map.Entry<String, String>> it = whereMap.entrySet().iterator(); it.hasNext();) {
            Map.Entry<String, String> entry = it.next();

            if (StringUtils.isBlank(entry.getValue())) {
                continue;
            }

            buff.append(" AND ").append("\"").append(entry.getKey().toLowerCase()).append("\"").append("=").append("'")
                    .append(entry.getValue()).append("' ");
        }

        return buff.toString();
    }

    public static void batchMapSort(List<Map<String, String>> data) {
        for (int i = 0; i < data.size(); i++) {
            Map<String, String> entry = data.get(i);
            TreeMap<String, String> map = new TreeMap<String, String>(new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });

            map.putAll(entry);

            data.set(i, map);
        }

    }

    public static String selectFields(String tablename, Set<String> fields, Map<String, String> whereMap, int limit,
            int offset, boolean useLike, String whereSql,String orderBySql) {
        if (StringUtils.isBlank(tablename)) {
            throw new NullPointerException();
        }

        StringBuilder buff = new StringBuilder();
        buff.append("SELECT ");

        if (fields == null || fields.isEmpty()) {
            buff.append(" * ");
        } else {
            for (String field : fields) {
                buff.append("\"").append(field).append("\"").append(",");
            }
        }
        buff.deleteCharAt(buff.length() - 1);

        buff.append(" FROM ").append(tablename).append(" WHERE 1=1 ");
        for (Iterator<Map.Entry<String, String>> it = whereMap.entrySet().iterator(); it.hasNext();) {
            Map.Entry<String, String> entry = it.next();

            if (StringUtils.isBlank(entry.getValue())) {
                continue;
            }

            if (useLike) {
                buff.append(" AND ").append("\"").append(entry.getKey().toLowerCase()).append("\"").append(" like ")
                        .append("'%").append(entry.getValue()).append("%' ");
            } else {
                buff.append(" AND ").append("\"").append(entry.getKey().toLowerCase()).append("\"").append("=")
                        .append("'").append(entry.getValue()).append("' ");
            }
        }

        if (!StringUtils.isBlank(whereSql)) {
            buff.append(" ").append(whereSql).append(" ");
        }

        if(StringUtils.isNotBlank(orderBySql)){
            buff.append(" ").append(orderBySql).append(" ");
        }

        if (limit > 0 && offset >= 0) {
            buff.append(" LIMIT ").append(limit).append(" OFFSET ").append(offset);
        }

        return buff.toString();
    }

    public static String selectFieldsCount(String tablename, Map<String, String> whereMap, boolean useLike,
            String whereSql) {
        if (StringUtils.isBlank(tablename)) {
            throw new NullPointerException();
        }

        StringBuilder buff = new StringBuilder();
        buff.append("SELECT count(*) c FROM ").append(tablename).append(" WHERE 1=1 ");
        for (Iterator<Map.Entry<String, String>> it = whereMap.entrySet().iterator(); it.hasNext();) {
            Map.Entry<String, String> entry = it.next();

            if (StringUtils.isBlank(entry.getValue())) {
                continue;
            }

            if (useLike) {
                buff.append(" AND ").append("\"").append(entry.getKey().toLowerCase()).append("\"").append(" like ")
                        .append("'%").append(entry.getValue()).append("%' ");
            } else {
                buff.append(" AND ").append("\"").append(entry.getKey().toLowerCase()).append("\"").append("=")
                        .append("'").append(entry.getValue()).append("' ");
            }
        }

        if (!StringUtils.isBlank(whereSql)) {
            buff.append(whereSql);
        }

        return buff.toString();
    }

    public static String exists(String tablename, Map<String, String> whereMap) {
        if (StringUtils.isBlank(tablename) || (whereMap==null || whereMap.isEmpty()) ) {
            throw new NullPointerException();
        }

        StringBuilder buff = new StringBuilder();
        buff.append("SELECT count(*) c FROM ").append(tablename).append(" WHERE 1=1 ");
        for (Iterator<Map.Entry<String, String>> it = whereMap.entrySet().iterator(); it.hasNext();) {
            Map.Entry<String, String> entry = it.next();

            if (StringUtils.isBlank(entry.getValue())) {
                continue;
            }

            buff.append(" AND ").append("\"").append(entry.getKey().toLowerCase()).append("\"").append("=")
                    .append("'").append(entry.getValue()).append("' ");
        }

        return buff.toString();
    }
}
